文档首页

全部产品

TDSQL-A PostgreSQL 版

租户端数据库 TDSQL-A PostgreSQL 版开发指南

设计规范

最近更新时间: 2025-02-18 16:02:00

命名规范

DB object:database、schema、table、column、view、index、sequence、function、trigger 等名称：
- 建议使用小写字母、数字、下划线的组合。
- 建议不使用双引号"包围，除非必须包含大写字母或空格等特殊字符。
- 长度不能超过63个字符。
- 不建议以 pg_ 开头或者 pgxc_（避免与系统 DB object 混淆），不建议以数字开头。
- 禁止使用 SQL 关键字，例如 type、order 等。
table 能包含的 column 数目，根据字段类型的不同，数目在250到1600之间。
临时或备份的 DB object:table、view 等，建议加上日期，如 dbaa_ops.b2c_product_summay_2014_07_12 (其中 dba_ops 为 DBA 专用 schema)。
index 命名规则：普通索引为表名_列名_idx，唯一索引为表名_列名_uidx，如 student_name_idx,student_id_uidx。

COLUMN 设计

建议使用数值类型，不用字符类型。
建议使用 varchar(N) ，不用 char(N)，以节省存储空间。
建议使用 varchar(N) ，不用 text、varchar。
建议使用 default NULL，不用default ''，以节省存储空间。
建议国际业务时，使用 timestamp with time zone(timestamptz)，不用 timestamp without time zone，避免时间函数在不同时区的时间点返回值不同，也为业务国际化扫清障碍。
建议使用 NUMERIC(precision, scale) 来存储货币金额和其它要求精确计算的数值，不建议使用 real、double precision。

Constraints 设计

建议每个 table 都使用 shard key 作为主键或者唯一索引，主键或唯一索引必须带着分布键。
建议建表时一步到位，一起建立主键或者唯一索引。
目前列存表暂不支持外键

Index 设计

TDSQL-A for PostgreSQL版提供的 index 类型：B-tree、Hash、GiST (Generalized Search Tree)、SP-GiST (space-partitioned GiST)、GIN (Generalized Inverted Index)、BRIN (Block Range Index)，目前不建议使用 Hash，通常情况下使用 B-tree。目前列存表只支持B-tree和Hash索引。
建议 create 或 drop index 时，加 CONCURRENTLY 参数，可以达到与写入数据并发的效果。
建议对频繁 update、delete 的包含于 index 定义中的 column 的 table，使用 create index CONCURRENTLY、drop index CONCURRENTLY 的方式进行维护其对应 index。
建议用 unique index 代替 unique constraints，便于后续维护。
建议对 where 中带多个字段 and 条件的高频 query，参考数据分布情况，建多个字段的联合 index。
建议对固定条件的（一般有特定业务含义）且数据占比低的 query，建议带 where 的 Partial Indexes。

  select * from test where status=1 and col=?; -- 其中status=1为固定的条件
create index on test (col) where status=1;

建议对经常使用表达式作为查询条件的 query，可以使用表达式或函数索引加速 query。

  select * from test where exp(xxx);
create index on test ( exp(xxx) );

建议不要建过多 index，一般不要超过6个，核心 table（产品，订单）可适当增加 index 个数。

关于 NULL

NULL 的判断：IS NULL、IS NOT NULL。
注意 boolean 类型取值 true、false、NULL。
注意 NOT IN 集合中带有 NULL 元素。

postgres=# select * from tdapg;
id |   nickname    
----+-------------------------------
1 | hello TDSQL-A for PostgreSQL
2 | TDSQL-A for PostgreSQL好
3 | TDSQL-A for PostgreSQL好
4 | TDSQL-A for PostgreSQL default
(4 rows)
postgres=# select * from tdapg where id not in (null);
id | nickname 
----+----------
(0 rows)

建议对字符串型 NULL 值处理后，进行 || 操作。

postgres=# select id,nickname from tdapg limit 1;
id |  nickname   
----+-----------------------------
1 | hello TDSQL-A for PostgreSQL
(1 row) 
postgres=# select id,nickname||null from tdapg limit 1;
id | ?column? 
----+----------
1 | 
(1 row) 
postgres=# select id,nickname||coalesce(null,'') from tdapg limit 1;
id |  ?column?   
----+-----------------------------
1 | hello TDSQL-A for PostgreSQL
(1 row)

建议使用 count(1) 或 count(*) 来统计行数，不建议使用 count(col) 来统计行数，因为 NULL 值不会计入。

注意：

count(多列列名)时，多列列名必须使用括号，例如 count( (col1,col2,col3) )，注意多列的 count，即使所有列都为 NULL，该行也被计数，所以效果与 count(*) 一致。

postgres=# select * from tdapg ;
id |   nickname    
----+-------------------------------
1 | hello TDSQL-A for PostgreSQL
2 | TDSQL-A for PostgreSQL好
5 | 
3 | TDSQL-A for PostgreSQL好
4 | TDSQL-A for PostgreSQL default
(5 rows)
postgres=# select count(1) from tdapg;
count 
\-------
   5
(1 row) 
postgres=# select count(*) from tdapg; 
count 
\-------
   5
(1 row)     
postgres=# select count(nickname) from tdapg;        
count 
\-------
   4
(1 row)
postgres=# select count((id,nickname)) from tdapg;   
count 
\-------
   5
(1 row)

count(distinct col) 计算某列的非 NULL 不重复数量，NULL 不被计数。

注意：

count(distinct (col1,col2,...) ) 计算多列的唯一值时，NULL 会被计数，同时 NULL 与 NULL会被认为是相同的。

postgres=# select count(distinct nickname) from tdapg;             
count 
\-------
   3
(1 row)
postgres=# select count(distinct (id,nickname)) from tdapg; 
count 
\-------
   5
(1 row)
两个 NULL 的对比方法。
postgres=# select null is not  distinct from null as Tdapgnull;      
Tdapgnull 
\-----------
t
(1 row)

开发相关规范

1）建议对 DB object 尤其是 COLUMN 加 COMMENT，便于后续新人了解业务及维护。

注释前后的数据表可读性对比：

postgres=# \d+ TDAPG_main
                    Table "public.tdapg_main"
Column |  Type   | Modifiers | Storage  | Stats target | Description 
--------+---------+-----------+----------+--------------+-------------
id     | integer |           | plain    |              | 
mc     | text    |           | extended |              | 
Indexes:
  "TDAPG_main_id_uidx" UNIQUE, btree (id)
Has OIDs: no
Distribute By SHARD(id)
      Location Nodes: ALL DATANODES
postgres=# comment on column TDAPG_main.id is 'id号';
COMMENT
postgres=# comment on column TDAPG_main.mc is '产品名称';
COMMENT
postgres=# \d+ TDAPG_main
                    Table "public.tdapg_main"
Column |  Type   | Modifiers | Storage  | Stats target | Description 
--------+---------+-----------+----------+--------------+-------------
id     | integer |           | plain    |              | id号
mc     | text    |           | extended |              | 产品名称
Indexes:
  "TDAPG_main_id_uidx" UNIQUE, btree (id)
Has OIDs: no
Distribute By SHARD(id)
      Location Nodes: ALL DATANODES

2）建议非必须时避免 select *，只取所需字段，以减少（包括不限于）网络带宽消耗。

postgres=#  explain (verbose) select * from tdapg_main where id=1;
                                       QUERY PLAN                                          
\---------------------------------------------------------------------------------------------
Index Scan using Tdapg_main_id_uidx on public.tdapg_main  (cost=0.15..8.17 rows=1 width=36)
 Output: id, mc
 Index Cond: (Tdapg_main.id = 1)
(3 rows) 
postgres=#  explain (verbose) select tableoid from tdapg_main where id=1;   
                                       QUERY PLAN                                         
\--------------------------------------------------------------------------------------------
Index Scan using Tdapg_main_id_uidx on public.tdapg_main  (cost=0.15..8.17 rows=1 width=4)
 Output: tableoid
 Index Cond: (Tdapg_main.id = 1)
(3 rows)

是返回36个字符，而另一个一条记录只能4个字段的长度。

3）建议 update 时，尽量做 <> 判断，如 update table_a set column_b = c where column_b <> c；

postgres=# update tdapg_main set mc='TDSQL-A for PostgreSQL' ;         
UPDATE 1
postgres=# select xmin,* from tdapg_main;
xmin | id |  mc   
------+----+-----------------------
2562 |  1 | TDSQL-A for PostgreSQL
(1 row) 
postgres=# update tdapg_main set mc='TDSQL-A for PostgreSQL' ;
UPDATE 1
postgres=# select xmin,* from tdapg_main;    
xmin | id |  mc   
------+----+-----------------------
2564 |  1 | TDSQL-A for PostgreSQL
(1 row)
postgres=# update tdapg_main set mc='TDSQL-A for PostgreSQL' where mc!='TDSQL-A for PostgreSQL';
UPDATE 0
postgres=# select xmin,* from tdapg_main;                     
xmin | id |  mc   
------+----+-----------------------
2564 |  1 | TDSQL-A for PostgreSQL
(1 row)

以上效果是一样的，但带条件的更新不会产生一个新的版本记录，不需要系统执行 vacuum 回收垃圾数据。

4）建议将单个事务的多条 SQL 操作，分解、拆分，或者不放在一个事务里，让每个事务的粒度尽可能小，尽量 lock 少的资源，避免 lock 、dead lock 的产生。

#session1 把所有数据都更新而不提交，一下子锁了2000千万条记录。

postgres=# begin;
BEGIN
postgres=# update tdapg_main set mc='TDAPG_1.3';
UPDATE 20000000

#session2 等待。

postgres=# update tdapg_main set mc='TDAPG_1.4'  where id=1;

#session3 等待。

postgres=# update tdapg_main set mc='TDAPG_1.5'  where id=2;

如果#session1分批更新的话，如下所示：

postgres=# begin;
BEGIN
postgres=# update tdapg_main set mc='TDAPG_1.3' where id>0 and id <=100000;
UPDATE 100000
postgres=#COMMIT; 
postgres=# begin;
BEGIN
postgres=# update tdapg_main set mc='TDAPG_1.3' where id>100000 and id <=200000;
UPDATE 100000
postgres=#COMMIT;

则 session2 和 session3 中就能部分提前完成，这样可以避免大量的锁等待和出现大量的 session 占用系统资源，在做全表更新时请使用这种方法来执行。

5）建议大批量的数据入库时，使用 copy ，不建议使用 insert，以提高写入速度。如下，性能相差5倍。

postgres=# insert into tdapg_main select t,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' from generate_series(1,100000) as t;
INSERT 0 100000
Time: 9511.755 ms
postgres=# copy  TDAPG_main to '/data/pgxz/TDAPG_main.txt';      
COPY 100002
Time: 179.428 ms
postgres=# copy  TDAPG_main from  '/data/pgxz/TDAPG_main.txt';
COPY 100002
Time: 1625.803 ms
postgres=#

6）建议对报表类的或生成基础数据的查询，使用物化视图 (MATERIALIZED VIEW) 定期固化数据快照，避免对多表（尤其多写频繁的表）重复跑相同的查询，且物化视图支持 REFRESH MATERIALIZED VIEW CONCURRENTLY，支持并发更新。

如有一个程序需要不断查询 TDAPG_main 的总记录数，可参考如下：

postgres=# select count(1) from tdapg_main;
count  
\--------
200004
(1 row)
Time: 27.948 ms
postgres=# create MATERIALIZED VIEW TDAPG_main_count as select count(1) as num from tdapg_main;
SELECT 1
Time: 322.372 ms
postgres=# select num from  TDAPG_main_count ;
 num   
\--------
200004
(1 row)
Time: 0.421 ms

性能提高上百倍。

有数据变化时刷新方法：

postgres=#  copy  tdapg_main from  '/data/pgxz/TDAPG_main.txt';
COPY 100002
Time: 1201.774 ms
postgres=# select count(1) from tdapg_main;
count  
\--------
300006
(1 row)
Time: 23.164 ms
postgres=# REFRESH MATERIALIZED VIEW TDAPG_main_count;         
REFRESH MATERIALIZED VIEW
Time: 49.486 ms
postgres=# select num from tdapg_main_count ;
 num   
\--------
300006
(1 row)
Time: 0.301 ms

7）建议复杂的统计查询可以尝试窗口函数。

请参见窗口函数应用。

8）两表 join 时，尽量的使用分布 key 进行 join。

在建立业务的主表、明细表时，需要使用他们的关联键来做分布键，如下所示：

[pgxz@VM_0_29_centos pgxz]$ psql -p 15001              
psql (PostgreSQL 10 (TDAPG 2.01))
Type "help" for help.
postgres=# create table tdapg_main(id integer,mc text) distribute by shard(id);
CREATE TABLE
postgres=# create table tdapg_detail(id integer,tdapg_main_id integer,mc text) distribute by shard(TDAPG_main_id);   
CREATE TABLE
postgres=# explain select TDAPG_detail.* from tdapg_main,TDAPG_detail where TDAPG_main.id=TDAPG_detail.TDAPG_main_id;       
                                QUERY PLAN                                 
\----------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__"  (cost=0.00..0.00 rows=0 width=0)
  Node/s: dn001, dn002
(2 rows) 
postgres=# explain (verbose) select TDAPG_detail.* from tdapg_main,TDAPG_detail where TDAPG_main.id=TDAPG_detail.TDAPG_main_id; 
                                                                                    QUERY PLAN                                                                                     
\------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__"  (cost=0.00..0.00 rows=0 width=0)
  Output: TDAPG_detail.id, TDAPG_detail.TDAPG_main_id, TDAPG_detail.mc
  Node/s: dn001, dn002
  Remote query: SELECT TDAPG_detail.id, TDAPG_detail.TDAPG_main_id, TDAPG_detail.mc FROM public.TDAPG_main, public.TDAPG_detail WHERE (TDAPG_main.id = TDAPG_detail.TDAPG_main_id)
(4 rows)
postgres=#

9）分布键用唯一索引代替主键。

postgres=# create unique index TDAPG_main_id_uidx on TDAPG_main using btree(id);
CREATE INDEX

因为唯一索引后期的维护成本比主键要低很多。

10）分布键无法建立唯一索引，则要建立普通索引，提高查询的效率。

postgres=# create index TDAPG_detail_TDAPG_main_id_idx on TDAPG_detail using btree(TDAPG_main_id);                   
CREATE INDEX

这样两表在 join 查询时，返回少量数据时的效率才会高。

11）不对字段建立外键。

目前 T 不支持多 dn 外键约束，除非能确定数据关联键的数据全部落在同一个 dn 上面。